import pandas as pd
import numpy as np
month = '202411'
# pre_year = '202312'
# pre_month = '202410'
pre_year_int = int(int(month[0:4]) - 1)
pre_year = str(pre_year_int) + '12'
if month[4:6] != '01':
    pre_month = str(int(int(month) - 1))
else:
    pre_month = str(pre_year_int) + '12'
month_int = int(month[4:6])
# 税率
tax_rate = 0.25
# month_int = 11
# Sheet3
# 补充一个参数
xlsx_name = 'D:/repos/sicost/' + month + '/吨钢利润,吨钢期间费用.xlsx'
df_1 = pd.read_excel(xlsx_name)
df_1 = df_1[['SERIAL_NUM', 'COMPANY_NAME', 'CURR_YEAR_VALUE_1', 'LAST_YEAR_VALUE_1']]
df_1 = df_1.reset_index(drop=False)
df_1.rename(columns={'index': 'INDEX'}, inplace=True)
df_1_1 = df_1[df_1['COMPANY_NAME'] == '非钢铁企业合计']
df_1_1 = df_1_1.reset_index(drop=True)

success = df_1_1.empty is False
if success is False:
    print('空的不需要处理')
else:
    index_tmp = df_1_1.loc[0]['INDEX']
    df_1_2 = df_1[df_1['INDEX'] < index_tmp]
    df_1_2 = df_1_2.reset_index(drop=True)
    df_1 = df_1_2
df_1.drop(['INDEX'], axis=1, inplace=True)

df_1.rename(columns={'CURR_YEAR_VALUE_1': '吨钢利润'}, inplace=True)
df_1.rename(columns={'LAST_YEAR_VALUE_1': '同比去年吨钢利润'}, inplace=True)


xlsx_name = 'D:/repos/sicost/' + month + '/实现利税,利润总额.xlsx'
df_2 = pd.read_excel(xlsx_name)
df_2 = df_2[['COMPANY_NAME', 'CURR_YEAR_VALUE_2', 'LAST_YEAR_VALUE_2']]
df_2.rename(columns={'CURR_YEAR_VALUE_2': '利润总额'}, inplace=True)
df_2.rename(columns={'LAST_YEAR_VALUE_2': '同比去年利润总额'}, inplace=True)

xlsx_name = 'D:/repos/sicost/' + month + '/研发费用,当期计提折旧额.xlsx'
df_3 = pd.read_excel(xlsx_name)
df_3 = df_3[['COMPANY_NAME', 'CURR_YEAR_VALUE_2', 'LAST_YEAR_VALUE_2']]
df_3.rename(columns={'CURR_YEAR_VALUE_2': '折旧'}, inplace=True)
df_3.rename(columns={'LAST_YEAR_VALUE_2': '同比去年折旧'}, inplace=True)

xlsx_name = 'D:/repos/sicost/' + month + '/财务费用,利息支出.xlsx'
df_4 = pd.read_excel(xlsx_name)
df_4 = df_4[['COMPANY_NAME', 'CURR_YEAR_VALUE_2', 'LAST_YEAR_VALUE_2']]
df_4.rename(columns={'CURR_YEAR_VALUE_2': '利息支出'}, inplace=True)
df_4.rename(columns={'LAST_YEAR_VALUE_2': '同比去年利息支出'}, inplace=True)

v = ['COMPANY_NAME']
df_0 = pd.merge(df_1, df_2, on=v, how='left')
df_0 = pd.merge(df_0, df_3, on=v, how='left')
df_0 = pd.merge(df_0, df_4, on=v, how='left')
df_0['年月'] = month
df_0['吨钢利润'] = df_0['吨钢利润'].fillna(0)
df_0['利润总额'] = df_0['利润总额'].fillna(0)
df_0['折旧'] = df_0['折旧'].fillna(0)
df_0['利息支出'] = df_0['利息支出'].fillna(0)
df_0['同比去年吨钢利润'] = df_0['同比去年吨钢利润'].fillna(0)
df_0['同比去年利润总额'] = df_0['同比去年利润总额'].fillna(0)
df_0['同比去年折旧'] = df_0['同比去年折旧'].fillna(0)
df_0['同比去年利息支出'] = df_0['同比去年利息支出'].fillna(0)
desired_order = ['年月', 'SERIAL_NUM', 'COMPANY_NAME', '吨钢利润', '利润总额', '折旧', '利息支出', '同比去年吨钢利润', '同比去年利润总额',
                 '同比去年折旧', '同比去年利息支出']
df_0 = df_0.reindex(columns=desired_order)

writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/Sheet6.xlsx')
df_0.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

print('finish')
